package com.lswms.modules.lswms.dao.task;

import java.math.BigDecimal;
import java.util.List;
import java.util.Map;
import java.util.Objects;

import com.lswms.modules.lswms.entity.receipt.ReceiptEntity;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.lswms.common.dao.BaseDao;
import com.lswms.modules.lswms.entity.task.TaskEntity;

/**
 * 任务
 *
 * @author DX sunlightcs@gmail.com
 * @since 1.0.0 2021-06-22
 */
@Mapper
public interface TaskDao extends BaseDao<TaskEntity> {


	@Select(value="select  max(a.priority) priority,  tasktype ,fromlocation,tolocation,fromtray,totray  ,"
			+ "			b.lane  fromlane,b.ddjrow fromrow ,b.ddjcolumn fromcolumn,b.ddjlevel fromlevel,"
			+ "		c.lane  tolane,c.ddjrow torow,c.ddjcolumn tocolumn,c.ddjlevel tolevel,max(d.listname) export"
			+ "	 from lswms_task  a  left join  lswms_location b on  a.fromlocation = b.location  and b.loc_category='STEREO'"
			+ "			left join lswms_location c on a.tolocation = c.location  and c.loc_category='STEREO'"
			+ "			left join lswms_codelist d on a.ip = d.list and d.code ='IPTOPORT'"
			+ "  where  a.status = 0  and isnull(sendflag,0)=0"
			+ "	 group by tasktype ,fromlocation,tolocation,fromtray,totray  ,b.lane ,b.ddjrow  ,b.ddjcolumn ,b.ddjlevel ,c.lane ,c.ddjrow ,c.ddjcolumn ,c.ddjlevel "
			+ "  order by priority, a.tasktype  , a.fromtray  ")
	List<Map<String,Object>> getUndoTask();

	@Select("select * from tb_task where task_status < 9 and tary_from = #{tary_from}")
	List<Map<String,Object>> getWCSTaskRepetition(Map<String ,Object> map);

	@Insert(value="insert into tb_task (task_id,task_type,task_status,priority,locate_from,locate_to,tary_from,tary_to,fromlane,GETX,	"
			+ " GETY,GETZ,tolane,PUTX,PUTY,PUTZ,create_date,type,export) "
			+ " values(#{task_id},#{task_type},#{task_status},#{priority},#{locate_from},#{locate_to},#{tary_from},#{tary_to},#{fromlane},#{GETX},"
			+ " #{GETY},#{GETZ},#{tolane},#{PUTX},#{PUTY},#{PUTZ} ,getdate(),#{type},#{export} )")
	void sendWCS(Map<String, Object> wcsTaskMap);


	@Update(value=" update lswms_task set taskno=#{task_id},sendflag=5,sendtime=getdate() where status=0  and isnull(sendflag,0)=0 and fromtray =#{tary_from}  ")
	void updateWMSTaskFlag(Map<String, Object> wcsTaskMap);


	@Select(value=" select * from tb_task where task_status >= 9 or (task_status >= 7 and task_type = 2) ")
	List<Map<String, Object>> getWCSDoneTask();

	@Insert(value=" INSERT INTO tb_task_record"
			+ "     (task_id,task_type,task_status,material_code,material_name,priority,type,locate_from,locate_to,locate_lane_no,"
			+ "		   lot_code,tary_from,tary_to,qty,start_time,end_time,uuid,remark,creator,create_date,updater,update_date,"
			+ "		   GETX,GETY,GETZ,PUTX,PUTY,PUTZ,fromlane,tolane,backups_date,manual)"
			+ " values(#{task_id},#{task_type},#{task_status},#{material_code},#{material_name},#{priority},#{type},#{locate_from},#{locate_to},#{locate_lane_no},"
			+ " #{lot_code},#{tary_from},#{tary_to},#{qty},#{start_time},#{end_time},#{uuid},#{remark},#{creator},#{create_date},#{updater},#{update_date},"
			+ " #{GETX},#{GETY},#{GETZ},#{PUTX},#{PUTY},#{PUTZ},#{fromlane},#{tolane},getdate(),#{manual} )")
	void insertRecord(Map<String, Object> map);


	@Delete(value="delete from tb_task where task_id=#{task_id} and task_status =#{task_status} ")
	void deleteWCSTask(Map<String, Object> map);

	@Select(value ="select * from lswms_task a join  lswms_lot b on a.tolot = b.lot  ${ew.customSqlSegment}")
	List<TaskEntity> selectTaskAndLot(@Param(Constants.WRAPPER) QueryWrapper<TaskEntity> taskToQuery);

	@Select("select a.tasktype , count(1) allcount ,sum(case when a.status=9 then 0 else 1 end) undo \n"
			+ "from ( select distinct taskno,tasktype,status from  lswms_task  where addtime > convert(varchar(10),Getdate(),120))a\n"
			+ "group by a.tasktype")
	List<Map<String, Object>> getStatusList();

	@Select( " select lswms_task.* ,a.listname tasktypestr , b.listname statusstr ,c.listname fromboxstr ,d.listname toboxstr  ,  lswms_sku.descr "
			+ "  from lswms_task left join lswms_codelist a on lswms_task.tasktype = a.list and a.code='TASKTYPE' "
			+ "			left join lswms_codelist b on lswms_task.status = b.list and b.code='TASKSTATUS' "
			+ "			left join lswms_codelist c on lswms_task.frombox = c.list and c.code='BOXLIST' "
			+ "			left join lswms_codelist d on lswms_task.tobox = d.list and d.code='BOXLIST' "
			+ "			left join lswms_sku  on lswms_task.sku = lswms_sku.sku and d.code='BOXLIST'  ${ew.customSqlSegment}  ")
	IPage<TaskEntity> selectPageBySql(IPage<TaskEntity> page, @Param(Constants.WRAPPER)QueryWrapper<TaskEntity> wrapper);

	@Select("select aa.zonecode ,aa.zonename , isnull(bb.count1 ,0) total ,isnull(cc.used,0) used\n"
			+ " from lswms_zone aa\n"
			+ "	left join \n"
			+ "	( select  zonecode ,count(1) count1 from lswms_location    group by zonecode ) bb on aa.zonecode = bb.zonecode\n"
			+ "	left join \n"
			+ "	(select zonecode ,count(1) used from lswms_location where location in ( select location from lswms_inventory where qty>0 or putqty >0 or pickqty>0 )  \n"
			+ " group by zonecode) cc  on aa.zonecode = cc.zonecode\n"
			+ " where aa.zonecode in ('AA','AB','BA','BB','BC') ")
	List<Map<String, Object>> getLocationUse();

	@Select("select  b.sort categorykey ,sum(a.qty) qty\n"
			+ "from  lswms_inventory a ,lswms_sku b\n"
			+ "where a.sku = b.sku \n"
			+ "group by b.sort order by categorykey desc ")
	List<Map<String, Object>> getSkuCount();

	@Select("select id,code from WCS_ERROR_MSG ")
	List<Map<String, Object>> getErrorMsg();

	@Select("select COMMENTS ,err1,err2,a.DVC_IP,a.PORT from WCS_DVC a, WCS_DDJ b\n"
			+ "where a.DVC_NO = b.DVC_NO \n"
			+ "order by COMMENTS ")
	List<Map<String, Object>> getDdjList();

	@Select("select CONVERT(varchar(100), addtime, 23) date,tradetype,sum(qty) qty  \n"
			+ "from  lswms_trade  where addtime> GETDATE()-6   and tradetype in ('PA','RPA' ,'PICK')\n"
			+ "group by CONVERT(varchar(100), addtime, 23),tradetype\n"
			+ "order by CONVERT(varchar(100), addtime, 23),tradetype")
	List<Map<String, Object>> getLast7Days();

	@Select(" select  count(1) from tb_task where task_status<>9 and (tary_from =#{fromlane} or tary_to =#{fromlane}) ")
	Integer selectWCSUndoTask(String fromlane);

	
	@Select("select abs(sum(aa.qty)) qty , aa.addtime,aa.tradetype  from (\n"
			+ "				select qty, case when  tradetype = 'PA'  or tradetype =  'CANCELPA'or tradetype =  'RPA' then 'PA' when tradetype = 'PICK' and sourcetype='PY' then 'PY'  else tradetype end tradetype  ,format(addtime,'yyyy-MM-dd') addtime\n"
			+ "				from lswms_trade\n"
			+ "				where tradetype in ('PA','PICK','RPA','CANCELPA') and addtime >dateadd(day,-30,format(getdate()+1,'yyyy-MM-dd'))\n"
			+ "				) aa\n"
			+ "				group by  aa.addtime,aa.tradetype\n"
			+ "				order by aa.addtime asc ,aa.tradetype ")
	List<Map<String, Object>> getLast30Days();

	@Select("select abs(sum(aa.qty)) qty , aa.addtime,aa.tradetype  from (\n"
			+ "select qty, case when  tradetype = 'PA'  or tradetype =  'CANCELPA'or tradetype =  'RPA' then 'PA' when tradetype = 'PICK' and sourcetype='PY' then 'PY'  else tradetype end tradetype  ,format(addtime,'yyyy-MM') addtime\n"
			+ "from lswms_trade\n"
			+ "where tradetype in ('PA','PICK','RPA','CANCELPA') and addtime >dateadd(year,-1,format(getdate()+1,'yyyy-MM'+'-01'))\n"
			+ ") aa\n"
			+ "group by  aa.addtime,aa.tradetype\n"
			+ "order by aa.addtime asc ,aa.tradetype ")
	List<Map<String, Object>> getLastYear();


	@Select("select aa.lot6,  aa.sku,aa.descr, aa.chaoqi,sum(aa.qty) qty from (\n"
			+ " select   b.lot6,  a.sku,c.descr , a.qty   , DATEDIFF ( day,  b.lot1 , GETDATE() )- CAST(b.lot14 AS int) chaoqi\n"
			+ " from lswms_inventory a , lswms_lot b , lswms_sku c\n"
			+ " where a.sku = c.sku  and  a.lot = b.lot and isnull(b.lot14,'') <>'' and b.lot1 is not null\n"
			+ "		and  PATINDEX('%[^0-9]%', isnull(b.lot14,'')  )=0\n"
			+ "		and b.lot1 + CAST(b.lot14 AS int)-15 < GETDATE()\n"
			+ ")aa\n"
			+ "group by aa.lot6,  aa.sku,aa.descr, aa.chaoqi\n"
			+ "order by chaoqi desc")
	List<Map<String, Object>> getInventorAlarm();

	@Select("select top 10 b.descr,b.sku,b.factorycdoe, case when isnull(a.fromlot,'')!='' then  c.lot6 else d.lot6 end lot6 ,abs(a.qty) qty ,e.recipient,format( a.addtime,'HH:mm:ss') time,f.tolocation\n"//
			+ "from lswms_trade a join lswms_sku b on   a.sku = b.sku \n"
			+ "		left join  lswms_lot c on a.fromlot = c.lot\n"
			+ "		left join lswms_lot d on a.tolot = d.lot\n"
			+ "		left join ( select lswms_Recipients.recipientkey,lswms_RecipientsInfo.recipient from lswms_Recipients , \n"
			+ "				lswms_RecipientsInfo where lswms_Recipients.cardId = lswms_RecipientsInfo.cardId ) e\n"
			+ "				on a.sourcekey = e.recipientkey and a.sourcetype='PY'\n"
			+"		left join lswms_task f on a.sku = f.sku and a.totray = f.totray\n"
			+ "where \n"
			+ " a.addtime > format(getdate()-10,'yyyy-MM-dd')\n"
			+ "	and tradetype in ('PA','PICK','RPA') \n"
			+ "order by a.addtime desc")
	List<Map<String, Object>> getTodayTaskInfo();

	//@Select(value ="select a.* , b.listname typestr , c.listname  statusstr    from lswms_receipt a  left join lswms_codelist b on a.type = b.list and b.code='ReceiptType' "
	//		+ "			left join lswms_codelist c on a.status = c.list and c.code='receiptstatus'  ${ew.customSqlSegment}")
	//IPage<ReceiptEntity> selectPageBySql(IPage<ReceiptEntity> page, @Param(Constants.WRAPPER)QueryWrapper<ReceiptEntity> wrapper);

	@Select("select b.descr,b.sku,b.factorycdoe, case when isnull(a.fromlot,'')!='' then  c.lot6 else d.lot6 end lot6 ,abs(a.qty) qty ,e.recipient,"
			+ "convert(time(7),format( a.addtime,'HH:mm:ss')) as addtime,case when a.tolocation is null or a.tolocation='' then a.fromlocation  else a.tolocation end  tolocation\n"
			+ "from lswms_trade as a join lswms_sku as b on   a.sku = b.sku \n"
			+ "		left join  lswms_lot as c on a.fromlot = c.lot\n"
			+ "		left join lswms_lot as d on a.tolot = d.lot\n"
			+ "		left join ( select lswms_Recipients.recipientkey,lswms_RecipientsInfo.recipient from lswms_Recipients , \n"
			+ "				lswms_RecipientsInfo where lswms_Recipients.cardId = lswms_RecipientsInfo.cardId ) as e\n"
			+ "				on a.sourcekey = e.recipientkey and a.sourcetype='PY'\n"
			+ "where \n"
			+ " tradetype in ('PA','PICK','RPA')  \n"
			+ " and \n"
			+ " a.addtime > format(getdate(),'yyyy-MM-dd')\n"
			+ " ${ew.customSqlSegment}"
//			+ "order by a.addtime desc "
			+ "")
	IPage<TaskEntity> getTodayTaskInfo2(IPage<TaskEntity> page,@Param(Constants.WRAPPER)QueryWrapper<TaskEntity> wrapper);

	@Select("select isnull(sum(qty),0 )from lswms_inventory")
	Integer getSkuSum();

	@Select("select count(1) from lswms_task " +
			"${ew.customSqlSegment}")
	Integer getTaskTaryCount(@Param(Constants.WRAPPER)QueryWrapper<Integer> wrapper);

	@Select("select isnull(sum(weigth),0 )from lswms_tray \n"
			+ "where exists ( select 1 from lswms_inventory , lswms_location \n"
			+ "	where lswms_inventory.location =  lswms_location.location and lswms_location.loc_category='STEREO' "
			+ " and  lswms_tray.tray = lswms_inventory.tray  and lswms_inventory.qty>0)")
	BigDecimal getWeigth();

	@Select("select count(*) from lswms_location where loc_category = 'STEREO'")
	Integer getlocateCount();

	@Select("SELECT count(*) FROM LSWMS_LOCATION WHERE location in (\n" +
			" select location COUN from lswms_inventory where location LIKE ('______') group by location )")
	Integer getlocateSY();

}
